<%-- 
    Document   : index
    Created on : 24 Apr 12, 17:39:31
    Author     : Reynald
--%>

<%@page import="java.io.OutputStream"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.ResultSet"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>

<%
    String driver = "org.gjt.mm.mysql.Driver";
    Class.forName(driver).newInstance();

    Connection con=null;
    ResultSet rst=null;
    Statement stmt=null;

    try{
    String url="jdbc:mysql://localhost:3306/progin_171_13509008?user=root";
    con=DriverManager.getConnection(url);
    stmt=con.createStatement();
    }
    catch(Exception e){
    System.out.println(e.getMessage());
    }

    rst=stmt.executeQuery("select * from user");
%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Content List</title>
        <link rel="stylesheet" type="text/css" title="T1" href="stylesheet/progin.css" id="link_theme" />
	<link rel="stylesheet" type="text/css" title="T1" href="stylesheet/posting.css" />
		<meta name="description" content="RERGag, Make You Laughing" />
		<meta name="keywords" content="laugh" />
		<meta name="author" content="Robertus Theodore" />
		<meta http-equiv="content-type" content="text/html;charset=UTF-8" />
                <script type="text/javascript" src="script/home.js"></script>
    </head>
    <body>
        <div class="wrapper">
            <div class="header">
                <a href="index.htm" id="image"><img src="images/logo.png" alt="title" height="50"/></a>
                <h1>RERGAG - Just For Laugh</h1>
            </div>
            <div class="links">
                <ul>
                    <li><a href="registrasi.php" id="registrasi_link" >Registrasi</a></li>
                    <li><a href="index.php" id="home_link">Home</a></li>
                    <li><a href="register.php" id="daftar_link">Daftar</a></li>
                    <li><a href="posting.php" id="upload_link" class="active">Posting</a></li>
                </ul>
            </div>
        </div>
        <%-- Form filter --%>
        <form method="POST" action="contentlist.jsp">
            <label> Filter </label>
                <input type="text" name="filter"/>
            <label> Sort by : </label>
                <select name="sort">
                    <option value="popular">Most Popular</option>
                    <option value="comment">Most Commented</option>
                    <option value="new" selected="selected">Newest</option>
                </select>
            <input type="submit" value="Select"/>
        </form>
        <%-- End of Form filter --%>
        
        <%  
            String queryTag = "select * from tag";
            rst = stmt.executeQuery(queryTag);
            out.println ("<ul>");
            rst.beforeFirst();
            while (rst.next()) {
                    out.println ( "<li><a id=\"lol\" href=\"contentlist.jsp?tagname="+rst.getString("tag_name")+" \" > "+rst.getString("tag_name")+" </a></li>");
            }
            out.println ("</ul>");
            out.println("</div>");
        %>
            <div class="main_container" id="main_content">
        <%            
            String sort = "popular";
            sort = request.getParameter("sort");
            String querySort;
            String listfilter = request.getParameter("filter");
            String queryFilter; 
            String[] filterTag;
            String getlink = request.getParameter("tagname");
                        
            String user_name = (String)(session.getAttribute("user_name"));
            
            if(getlink != null){
                if(getlink.equals("")){
                    if(listfilter != null){
                        filterTag = listfilter.split(",");
                        if(filterTag[0].isEmpty()){
                            
                        }else{
                            for(int i = 0 ; i < filterTag.length ; i++){
                                String queryView = "CREATE VIEW upil AS SELECT * FROM content WHERE id_content = (SELECT id_content FROM tag_content WHERE id_tag ="+
                                        "(SELECT id_tag FROM tag WHERE tag_name = '"+filterTag[i]+"'))";
                                rst = stmt.executeQuery(queryView);
                                if(sort.equals("popular")){
                                    queryFilter = "Select upil.user_name, upil.title, upil.content_type, upil.content_filled, upil.posting_time from"+
                                            "(Select c.id_content as e from ((Select b.id_content from (select id_content,count(*) as result from like_content where status = 1 group by id_content order by result desc) as b)"+
                                            "union distinct"+
                                            "(select not_like_result.id_content as not_like_result_id_content from (select id_content from like_content where status = 0 or status = 2 )as not_like_result)) as c) as most_like_order_id_content"+
                                            ", upil"+
                                    "where most_like_order_id_content.e = id_content";
                                }else{
                                    if(sort.equals("comment")){
                                        queryFilter = "Select upil.user_name, upil.title, upil.content_type, upil.content_filled, upil.posting_time from upil inner join (select most_comment.id_content as id_most_commented from (select id_content, count(*) as result from comment group by id_content order by result Desc) as most_comment)  comment_result"+
                                            "on upil.id_content = comment_result.id_most_commented";
                                    }else{
                                        queryFilter = "select distinct upil.user_name, upil.title, upil.content_type, upil.content_filled from "+
                                            "(select id_content, TIMESTAMPDIFF(SECOND, posting_time, now()) as time from upil order by time) time_order, upil "+
                                            "order by time_order.time";
                                    }
                                }
                                rst = stmt.executeQuery(queryFilter);
                                rst.last();
                                int row = rst.getRow();
                                rst.beforeFirst();
                                if(row == 0){

                                }else{
                                    int number = 6;
                                    int pages = 0;
                                    //display content
                                    for(int index = 0 ; index < number ; index++){
                                        if(rst.getString("content_type").equals("link")){
                                            out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                            out.println("<h2>"+rst.getString("content_filled")+"</h2>");
                                            out.println("<h3>"+rst.getString("content_description")+"</h3>");
                                            out.println("<h3>Posted by:"+rst.getString("user_name")+"</h3><br/>");
                                        }
                                        if(rst.getString("content_type").equals("image")){
                                            out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                            out.println("<img src='"+rst.getString("content_filled")+"' height=500px;></img>");
                                            out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                                        }
                                        if(rst.getString("content_type").equals("video")){
                                            out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                            out.println(rst.getString("content_filled"));
                                            out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                                        }
                                    }
                                    //end of display content
                                }
                            }
                        }
                    }else{
                        
                    }
                }else{
                    //link tag
                    String queryView = "CREATE VIEW upil AS SELECT * FROM content WHERE id_content = (SELECT id_content FROM tag_content WHERE id_tag ="+
                        "(SELECT id_tag FROM tag WHERE tag_name = '"+getlink+"'))";
                    String queryLinkFilter;
                    int tes = stmt.executeUpdate(queryView);
                    if(sort != null){
                        if(sort.equals("popular")){
                            queryLinkFilter = "Select upil.user_name, upil.title, upil.content_type, upil.content_filled, upil.posting_time from"+
                                        "(Select c.id_content as e from ((Select b.id_content from (select id_content,count(*) as result from like_content where status = 1 group by id_content order by result desc) as b)"+
                                        "union distinct"+
                                        "(select not_like_result.id_content as not_like_result_id_content from (select id_content from like_content where status = 0 or status = 2 )as not_like_result)) as c) as most_like_order_id_content"+
                                        ", upil"+
                                "where most_like_order_id_content.e = id_content";
                        }else{
                            if(sort.equals("comment")){
                                queryLinkFilter = "Select upil.user_name, upil.title, upil.content_type, upil.content_filled, upil.posting_time from upil inner join (select most_comment.id_content as id_most_commented from (select id_content, count(*) as result from comment group by id_content order by result Desc) as most_comment)  comment_result"+
                                        "on upil.id_content = comment_result.id_most_commented";
                            }else{
                                queryLinkFilter = "select distinct upil.user_name, upil.title, upil.content_type, upil.content_filled from "+
                                        "(select id_content, TIMESTAMPDIFF(SECOND, posting_time, now()) as time from upil order by time) time_order, upil "+
                                        "order by time_order.time";
                            }
                        }
                        rst = stmt.executeQuery(queryLinkFilter);
                        rst.beforeFirst();
                        int number = 6;
                        int pages = 0;
                        //display content
                        while(rst.next()){
                            if(rst.getString("content_type").equals("link")){
                                out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                out.println("<h2>"+rst.getString("content_filled")+"</h2>");
                                out.println("<h3>"+rst.getString("content_description")+"</h3>");
                                out.println("<h3>Posted by:"+rst.getString("user_name")+"</h3><br/>");
                            }
                            if(rst.getString("content_type").equals("image")){
                                out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                out.println("<img src='"+rst.getString("content_filled")+"' height=500px;></img>");
                                out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                            }
                            if(rst.getString("content_type").equals("video")){
                                out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                out.println(rst.getString("content_filled"));
                                out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                            }
                        }
                                //end of display content
                        queryView = "drop VIEW upil";
                        tes = stmt.executeUpdate(queryView);
                    }else{
                        queryLinkFilter = "Select upil.user_name, upil.title, upil.content_type, upil.content_filled, upil.posting_time from"+
                                        "(Select c.id_content as e from ((Select b.id_content from (select id_content,count(*) as result from like_content where status = 1 group by id_content order by result desc) as b)"+
                                        "union distinct"+
                                        "(select not_like_result.id_content as not_like_result_id_content from (select id_content from like_content where status = 0 or status = 2 )as not_like_result)) as c) as most_like_order_id_content"+
                                        ", upil "+
                                "where most_like_order_id_content.e = id_content";
                        rst.beforeFirst();
                        rst = stmt.executeQuery(queryLinkFilter);
                        int number = 6;
                        int pages = 0;
                        //display content
                        while(rst.next()){
                            if(rst.getString("content_type").equals("link")){
                                out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                out.println("<h2>"+rst.getString("content_filled")+"</h2>");
                                out.println("<h3>"+rst.getString("content_description")+"</h3>");
                                out.println("<h3>Posted by:"+rst.getString("user_name")+"</h3><br/>");
                            }
                            if(rst.getString("content_type").equals("image")){
                                out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                out.println("<img src='"+rst.getString("content_filled")+"' height=500px;></img>");
                                out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                            }
                            if(rst.getString("content_type").equals("video")){
                                out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                out.println(rst.getString("content_filled"));
                                out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                            }
                        }
                                //end of display content
                        queryView = "drop VIEW upil";
                        tes = stmt.executeUpdate(queryView);
                    }
                }
            }else{
                //sort = popular
                if(listfilter != null){
                    filterTag = listfilter.split(",");
                    for(int i = 0 ; i < filterTag.length ; i++){                        
                        String queryView = "CREATE VIEW upil AS SELECT * FROM content WHERE id_content = (SELECT id_content FROM tag_content WHERE id_tag ="+
                                "(SELECT id_tag FROM tag WHERE tag_name = '"+filterTag[i]+"'))";
                        int iping = stmt.executeUpdate(queryView);
                        if( sort!= null){
                            if(sort.equals("popular")){
                                queryFilter = "Select upil.user_name, upil.title, upil.content_type, upil.content_filled, upil.posting_time from "+
                                "(Select c.id_content as e from ((Select b.id_content from (select id_content,count(*) as result from like_content where status = 1 group by id_content order by result desc) as b) "+
                                "union distinct "+
                                "(select not_like_result.id_content as not_like_result_id_content from (select id_content from like_content where status = 0 or status = 2 )as not_like_result)) as c) as most_like_order_id_content "+
                                ", upil "+
                        "where most_like_order_id_content.e = id_content";
                            }else{
                                if(sort.equals("comment")){
                                    queryFilter = "Select upil.user_name, upil.title, upil.content_type, upil.content_filled, upil.posting_time from upil inner join (select most_comment.id_content as id_most_commented from (select id_content, count(*) as result from comment group by id_content order by result Desc) as most_comment)  comment_result "+
                                "on upil.id_content = comment_result.id_most_commented";
                                }else{
                                    queryFilter = "select distinct upil.user_name, upil.title, upil.content_type, upil.content_filled from "+
                                        "(select id_content, TIMESTAMPDIFF(SECOND, posting_time, now()) as time from upil order by time) time_order, upil "+
                                        "order by time_order.time";
                                }
                            }
                        }else{
                            queryFilter = "select distinct upil.user_name, upil.title, upil.content_type, upil.content_filled from "+
                                        "(select id_content, TIMESTAMPDIFF(SECOND, posting_time, now()) as time from upil order by time) time_order, upil "+
                                        "order by time_order.time";
                        }
                        rst = stmt.executeQuery(queryFilter);
                        rst.last();
                        int row = rst.getRow();
                        rst.beforeFirst();
                        if(row == 0){

                        }else{
                            int number = 6;
                            int pages = 0;
                            //display content
                            while(rst.next()){
                                if(rst.getString("content_type").equals("link")){
                                    out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                    out.println("<h2>"+rst.getString("content_filled")+"</h2>");
                                    out.println("<h3>"+rst.getString("content_description")+"</h3>");
                                    out.println("<h3>Posted by:"+rst.getString("user_name")+"</h3><br/>");
                                }
                                if(rst.getString("content_type").equals("image")){
                                    out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                    out.println("<img src='"+rst.getString("content_filled")+"' height=500px;></img>");
                                    out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                                }
                                if(rst.getString("content_type").equals("video")){
                                    out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                                    out.println(rst.getString("content_filled"));
                                    out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                                }
                            }
                            //end of display content
                        }
                        queryView = "drop VIEW upil";
                        int upil = stmt.executeUpdate(queryView);
                    }
                }else{
                    querySort = "Select id_content as id_konten, user_name, content.title, content.content_type, content.content_filled, content.posting_time from"+
                                            "(Select c.id_content as e from ((Select b.id_content from (select id_content,count(*) as result from like_content where status = 1 group by id_content order by result desc) as b)"+
                                            "union distinct"+
                                            "(select not_like_result.id_content as not_like_result_id_content from (select id_content from like_content where status = 0 or status = 2 )as not_like_result)) as c) as most_like_order_id_content"+
                                            ", content "+
                                    "where most_like_order_id_content.e = id_content";
                    System.out.println(querySort);
                    rst = stmt.executeQuery(querySort);
                    rst.last();
                    int row = rst.getRow();
                    rst.beforeFirst();
                    int number = 6;
                    int pages = 0;
                    //display content
                    while(rst.next()){
                        if(rst.getString("content_type").equals("link")){
                            out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                            out.println("<h2>"+rst.getString("content_filled")+"</h2>");
                            out.println("<h3>"+rst.getString("content_description")+"</h3>");
                            out.println("<h3>Posted by:"+rst.getString("user_name")+"</h3><br/>");
                        }
                        if(rst.getString("content_type").equals("image")){
                            out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                            out.println("<img src='"+rst.getString("content_filled")+"' height=500px;></img>");
                            out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                        }
                        if(rst.getString("content_type").equals("video")){
                            out.println("<br/><h1><a href='detail.jsp'>"+rst.getString("title") +"</a></h1>");
                            out.println(rst.getString("content_filled"));
                            out.println("<h3>Posted by: <a href=detail.jsp'>"+rst.getString("user_name")+"</a></h3><br/>");
                        }
                    }
                }
            }
        %>
        </div>
        <div class="sidebar_container">
            
        </div>
    </body>
</html>
